<?php

/**
 *   OPENSHOP 销售明细列表程序
 * 
 *   @link        http://baison.com.cn
 *   @copyright   Baison, Inc.
 *   @package     OpenShop
 *   @version     $Id: sale_order_list.php,v 1.0 2009/06/17 09:55:58 modified $
 *   @author      winner <jsycywm@hotmail.com>
 */

define('IN_OS', true);

require(dirname(__FILE__) . '/includes/init.php');
require_once(ROOT_PATH . 'includes/lib_order.php');

/* act操作项的初始化 */
if (empty($_REQUEST['act']))
{
    $_REQUEST['act'] = 'list';
}
else
{
    $_REQUEST['act'] = trim($_REQUEST['act']);
}

/*------------------------------------------------------ */
//--数据查询
/*------------------------------------------------------ */
/* 时间参数 */
if (isset($_POST) && !empty($_POST))
{
    $start_date = local_strtotime($_POST['start_date']);
    $end_date = local_strtotime($_POST['end_date']);
}
elseif (isset($_GET['start_date']) && !empty($_GET['end_date']))
{
    $start_date = local_strtotime($_GET['start_date']);
    $end_date = local_strtotime($_GET['end_date']);
}
else
{
    $today  = local_strtotime(local_date('Y-m-d'));
    $start_date = $today - 86400 * 7;
    $end_date   = $today;
}

if ($_REQUEST['act'] == 'down_sales_list')
{
    $start_date = $_GET['start_date'];
    $end_date   = $_GET['end_date'];
}

/* 查询数据的条件 */
$goods_sales_list = array();
$where ="where oi.shipping_status = 1  AND oi.add_time >= '$start_date' AND oi.add_time < '" . ($end_date + 86400) . "'  " ; 
         
$sql="select oi.add_time, oi.order_from,oi.order_sn,oi.order_id,
		oi.pay_name,oi.pay_status,oi.lock_status,oi.consignee,oi.address,oi.email,tel,
		oi.extension_code,oi.extension_id,oi.shipping_name,oi.invoice_status, 
		(select ifnull(sum(goods_number),0) from open_order_goods as og where og.order_id=oi.order_id)  as sl,
		( oi.goods_amount - oi.discount + oi.tax + oi.shipping_fee + oi.pay_fee + oi.pack_fee + oi.card_fee - oi.bonus)  as shJE, 
		 CONCAT('[', u.user_name,']' ,u.nick_name) as nick_name ,
		( case oi.order_from when 7 then '官方网站' when 8 then 'Taobao订单' when 9 then 'PaiPai订单' when 10 then '易趣订单' when 11 then '有啊订单' else '官方网站' end)  as fromname
		from open_order_info as oi left join open_users as u on u.user_id=oi.user_id ".
		$where. "  ORDER BY add_time DESC";   
		 
 //退单sql
//		 $sql= "select o_r.add_time, 7 as order_from,
//				concat( o_r.return_sn,' [',o_r.relating_order_sn,']') as return_sn , 
//				id as return_id, '退还余额' as pay_name,
//				concat(o_r.return_consignee,'[',o_r.return_tel,']') as return_consignee,
//				o_r.return_address,    
//				( select ifnull(sum(goods_return_number),0) from open_order_return_goods o_r_g where o_r_g.return_order_id=o_r.id ) as sl,   
//				(select ifnull(sum(o_r_g.goods_price*o_r_g.goods_return_number),0) from open_order_return_goods o_r_g where o_r_g.return_order_id=o_r.id) as fee
//				from open_order_return  as o_r 
//				where o_r.return_order_status=5  "; 

	$res = $db->query($sql);  
	$sale_goods_count=0; 
	$sale_goods_money=0;
	$sql="select   
			sum((select ifnull(sum(goods_number),0) from open_order_goods as og where og.order_id=oi.order_id)) as sl,  
			sum(( oi.goods_amount - oi.discount + oi.tax + oi.shipping_fee + oi.pay_fee + oi.pack_fee + oi.card_fee - oi.bonus) )as shJE  
			from open_order_info as oi ".$where. "  ORDER BY add_time DESC";   ;
    $sum_row=$db->getRow($sql);

while ($items = $db->fetchRow($res))
{
    $items['sales_price']   = price_format($items['sales_price']);
    $items['sales_time']    = local_date($_CFG['time_format'], $items['sales_time']);
    $items['add_time']    = local_date($_CFG['time_format'], $items['add_time']);
    $goods_sales_list[]     = $items;
}

/*------------------------------------------------------ */
//--商品明细列表
/*------------------------------------------------------ */
if ($_REQUEST['act'] == 'list')
{ 
    /* 权限检查 */
    admin_priv('sale_order_list_view');
    
    /* 赋值到模板 */
    $smarty->assign('goods_sales_list', $goods_sales_list);
    
	$smarty->assign('sale_goods_count', $sum_row['sl']);
	$smarty->assign('sale_goods_money', $sum_row['shJE']); 
    $smarty->assign('start_date',   local_date('Y-m-d', $start_date));
    $smarty->assign('end_date',     local_date('Y-m-d', $end_date)); 
    $smarty->assign('ur_here',      '已发货订单统计');    
    $smarty->assign('action_link',  array('text' => '下载已发货订单',
    'href'=>'sale_order_list.php?act=down_sales_list&start_date='.$start_date.'&end_date='.$end_date));

    /* 显示页面 */
    assign_query_info();
    $from = trim($_REQUEST['from']);

    if ($from == '')
    {
        $from = trim($_POST['from']);
    }
    
    $smarty->assign('from',$from);    
    $smarty->display("sale_order_list.tpl");    
}

/*------------------------------------------------------ */
//--Excel文件下载
/*------------------------------------------------------ */
elseif ($_REQUEST['act'] == 'down_sales_list')
{
	/* 权限判断 */  
    admin_priv('sale_order_list_export');
    $file_name = $_REQUEST['start_date'].'_'.$_REQUEST['end_date'] . '_sale';

    header("Content-type: application/vnd.ms-excel; charset=utf-8");
    header("Content-Disposition: attachment; filename=$file_name.xls");

    /* 文件标题 */
    echo os_iconv(OS_CHARSET, 'GB2312', local_date('Y-m-d', $_REQUEST['start_date']). '至' .local_date('Y-m-d', $_REQUEST['end_date']) . '已发货订单统计') . "\t\n";

    /* 商品名称,订单号,商品数量,销售价格,销售日期 */
    echo os_iconv(OS_CHARSET, 'GB2312', '订单号') . "\t";
    echo os_iconv(OS_CHARSET, 'GB2312', '下单人') . "\t";
    echo os_iconv(OS_CHARSET, 'GB2312', '收货人') . "\t"; 
                
    echo os_iconv(OS_CHARSET, 'GB2312', '快递') . "\t";
    echo os_iconv(OS_CHARSET, 'GB2312', '数量') . "\t";
    echo os_iconv(OS_CHARSET, 'GB2312', '金额') .  "\t";
    echo os_iconv(OS_CHARSET, 'GB2312', '支付方式') .  "\t";
    echo os_iconv(OS_CHARSET, 'GB2312', '订单来由') .  "\t";
    echo os_iconv(OS_CHARSET, 'GB2312', '下单日期') . "\t\n";
 
    foreach ($goods_sales_list AS $key => $value)
    {
		echo os_iconv(OS_CHARSET, 'GB2312', $value['order_sn']) . "\t";  
		echo os_iconv(OS_CHARSET, 'GB2312', $value['nick_name']) . "\t";
		echo os_iconv(OS_CHARSET, 'GB2312', $value['consignee'].$value['tel'].$value['address']  ) . "\t"; 
		echo os_iconv(OS_CHARSET, 'GB2312', $value['shipping_name']) . "\t";
		echo os_iconv(OS_CHARSET, 'GB2312', $value['sl']) . "\t";
		echo os_iconv(OS_CHARSET, 'GB2312', $value['shJE']) . "\t";
		echo os_iconv(OS_CHARSET, 'GB2312', $value['pay_name']) . "\t";
		echo os_iconv(OS_CHARSET, 'GB2312', $value['fromname']) . "\t";
		echo os_iconv(OS_CHARSET, 'GB2312', $value['add_time']) . "\t";
        echo "\n";
    } 
	echo os_iconv(OS_CHARSET, 'GB2312', '' ) . "\t";  
	echo os_iconv(OS_CHARSET, 'GB2312', '') . "\t";
	echo os_iconv(OS_CHARSET, 'GB2312',  '' ) . "\t"; 
	echo os_iconv(OS_CHARSET, 'GB2312',  '' ) . "\t";
	echo os_iconv(OS_CHARSET, 'GB2312',  $sum_row['sl']).  "\t";
	echo os_iconv(OS_CHARSET, 'GB2312',  $sum_row['shJE']  ) . "\t";
	echo os_iconv(OS_CHARSET, 'GB2312',   '' ) . "\t";
	echo os_iconv(OS_CHARSET, 'GB2312',  ''  ) . "\t";
	echo os_iconv(OS_CHARSET, 'GB2312',   '' ) . "\t";
	echo "\n";
}
?>